package com.epam.task.web.service;

import com.epam.task.web.db.DataBaseReader;
import com.epam.task.web.entity.*;
import com.epam.task.web.util.logging.LoggerUtil;
import org.apache.log4j.Logger;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public abstract class AbstractAction {

    private final LoggerUtil logger = new LoggerUtil(Logger.getLogger(AbstractAction.class));

    public abstract String perform(HttpServletRequest request, HttpServletResponse response);

    protected ResultSet find(String tableName, String fieldName, String fieldValue) {
        DataBaseReader dataBaseReader = new DataBaseReader();
        String query = null;
        ResultSet resultSet = null;

        try {
            query = String.format("SELECT * FROM %s WHERE %s = '%s';", tableName, fieldName, fieldValue);
            resultSet = dataBaseReader.select(query);
            if (resultSet != null) {
                if (resultSet.next()) {
                    resultSet.beforeFirst();
                    return resultSet;
                }
            } else {
                return null;
            }
        } catch (SQLException exception) {
            logger.getExceptionTextFileLogger().error(exception);
        }

        return null;
    }

    protected ResultSet find(String tableName, Object... values) {
        DataBaseReader dataBaseReader = new DataBaseReader();
        ResultSet resultSet = null;

        try {
            resultSet = dataBaseReader.select(tableName, values);
            if (resultSet != null) {
                if (resultSet.next()) {
                    resultSet.beforeFirst();
                    return resultSet;
                }
            } else {
                return null;
            }
        } catch (SQLException exception) {
            logger.getExceptionTextFileLogger().error(exception);
        }

        return null;
    }

    protected Object findField(ResultSet resultSet, String fieldName) {
        try {
            resultSet.first();
            return resultSet.getObject(fieldName);
        } catch (SQLException e) {
            logger.getExceptionTextFileLogger().error(e);
            return null;
        }
    }

    protected User createUserById(long userId) {
        ResultSet resultSet = null;
        try {

            resultSet = find("oc_user", "user_id", userId);
            resultSet.first();

            long id = resultSet.getLong("user_id");
            String username = resultSet.getString("username");
            String firstName = resultSet.getString("firstname");
            String lastName = resultSet.getString("lastname");
            String password = resultSet.getString("password");
            String email = resultSet.getString("email");
            UserGroup group = createUserGroupById((resultSet.getLong("user_group_id")));

            return new User(id, username, firstName, lastName, password, email, group);
        } catch (SQLException exception) {
            logger.getExceptionTextFileLogger().error(exception);
            return null;
        }
    }

    protected UserGroup createUserGroupById(long userGroupId) {
        ResultSet resultSet = null;
        try {
            resultSet = find("oc_user_group", "user_group_id", userGroupId);
            resultSet.first();
            int id = resultSet.getInt("user_group_id");
            String position = String.valueOf(resultSet.getObject("name"));
            return new UserGroup(id, position);
        } catch (SQLException e) {

            e.printStackTrace();
            return null;
        }
    }

    protected Order createOrderById(long orderId) {
        ResultSet resultSet = null;
        try {
            resultSet = find("oc_order", "order_id", orderId);
            resultSet.first();

            long id = resultSet.getLong("order_id");
            Date dateAdded = resultSet.getDate("date_added");
            Status status = createStatusById(resultSet.getLong("order_status_id"));
            User customer = createUserById(resultSet.getLong("user_id"));
            User manager = createUserById(resultSet.getLong("manager_id"));
            String comment = resultSet.getString("comment");
            OrderProduct orderProduct = createOrderProductById(resultSet.getLong("order_id"));

            return new Order(id, dateAdded, status, customer, manager, comment, orderProduct);
        } catch (SQLException exception) {
            logger.getExceptionTextFileLogger().error(exception);
            return null;
        }
    }

    protected Status createStatusById(long statusId) {
        ResultSet resultSet;
        try {
            resultSet = find("oc_order_status", "order_status_id", statusId);
            resultSet.first();
            long id = resultSet.getLong("order_status_id");
            String status = resultSet.getString("name");
            return new Status(id, status);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    protected OrderProduct createOrderProductById(long orderId) {
        ResultSet resultSet;
        try {
            resultSet = find("oc_order_product", "order_id", orderId);
            resultSet.first();

            long id = resultSet.getLong("order_id");
            long orderProductId = resultSet.getLong("order_product_id");
            Product product = createItemById(resultSet.getLong("product_id"));
            int quantity = resultSet.getInt("quantity");
            BigDecimal price = resultSet.getBigDecimal("price");

            return new OrderProduct(id, orderProductId, product, quantity, price);
        } catch (SQLException exception) {
            logger.getExceptionTextFileLogger().error(exception);
            return null;
        }
    }

    protected Product createItemById(long productId) {
        ResultSet resultSet;
        try {
            resultSet = find("oc_product", "product_id", productId);
            resultSet.first();

            long id = resultSet.getLong("product_id");
            String name = resultSet.getString("name");
            int quantity = resultSet.getInt("quantity");
            String image = resultSet.getString("image");
            BigDecimal price = resultSet.getBigDecimal("price");
            Date dateAvailable = resultSet.getDate("date_available");
            Date dateAdded = resultSet.getDate("date_added");
            Date dateModified = resultSet.getDate("date_modified");
            int viewedCount = resultSet.getInt("viewed");
            int minimum = resultSet.getInt("minimum");
            int maximum = resultSet.getInt("maximum");

            return new Product(id, name, quantity, image, price, dateAvailable, dateAdded, dateModified, viewedCount, minimum, maximum);
        } catch (SQLException exception) {
            logger.getExceptionTextFileLogger().error(exception);
            return null;
        }
    }

}
